﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.ReportSource;
namespace KHAMBENH
{
    public partial class frmBaoCao : Form
    {
        
        public frmBaoCao()
        {
            InitializeComponent();
        }

        private void frmBaoCao_Load(object sender, EventArgs e)
        {
            txtTungay.Value = DateTime.Now;
            txtDenngay.Value = DateTime.Now;
        }

        private void btnTimkiem_Click(object sender, EventArgs e)
        {
            string connetionString = null;
            string sql = "";
            string strHoten = "(";
            DataSet myDataSet = new DataSet();
            string strTungay = Convert.ToDateTime(txtTungay.Value).Month + "/" + Convert.ToDateTime(txtTungay.Value).Day 
                + "/" + Convert.ToDateTime(txtTungay.Value).Year;
            string strDenngay = Convert.ToDateTime(txtDenngay.Value).Month + "/" + Convert.ToDateTime(txtDenngay.Value).Day
                + "/" + Convert.ToDateTime(txtDenngay.Value).Year;
            OleDbConnection cnn;
            connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=khambenh.mdb;";
            cnn = new OleDbConnection(connetionString);
            if (txtHoten.Text != "") { strHoten = strHoten + " (DONTHUOC.HOTEN) = '"  + txtHoten.Text + "' and "; }
            try 
            {
                sql = "SELECT DONTHUOC.HOTEN, DONTHUOC.GIOITINH, DONTHUOC.NAMSINH, "
                + " DONTHUOC.NGAYKHAM, DONTHUOC.DIACHI, DONTHUOC.CHUANDOAN,DONTHUOC.KHOA, DONTHUOC.LOIDAN, DONTHUOC.KEYSL "
                + " from DONTHUOC where  " + strHoten + " ngaykham >= #" + strTungay + "# and ngaykham <= #" + strDenngay + "#);" ;
                OleDbCommand myAccessCommand = new OleDbCommand(sql, cnn);
                OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
                cnn.Open();
                myDataAdapter.Fill(myDataSet, "tenthuoc");
                fa.DataSource = myDataSet.Tables["tenthuoc"];
                               
                cnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            formatfa();
        }
        private void formatfa()
        {
            fa.Cols["hoten"].WidthDisplay = 150;
            fa.Cols["diachi"].WidthDisplay = 200;
            fa.Cols["chuandoan"].WidthDisplay = 200;
  
        }
        private void fa_DoubleClick(object sender, EventArgs e)
        {
            frmKhambenh childForm = (frmKhambenh)GetForm("frmKhambenh");
            if (childForm == null)
            {
                childForm = new frmKhambenh();
                childForm.MdiParent = this.MdiParent;
                childForm.WindowState = FormWindowState.Maximized;
                childForm.Show();

            }
            else
            {
                childForm.Activate();
            }
            childForm.KEYSL = fa.Rows[fa.Selection.TopRow]["keysl"].ToString().Trim();
            childForm.SetValuetoControl();
         
        }
        private Form GetForm(String FormName)
        {
            foreach (Form f in this.FindForm().MdiChildren)
            {
                if (f.Name == FormName)
                    return f;
            }
            return null;
        }

        private void btnBaocao_Click(object sender, EventArgs e)
        {
            string connetionString = null;
            string sql = "";
            Dataset.dsDonthuoc dsDonthuoc = new Dataset.dsDonthuoc(); 
            string strTungay = Convert.ToDateTime(txtTungay.Value).Month + "/" + Convert.ToDateTime(txtTungay.Value).Day
                + "/" + Convert.ToDateTime(txtTungay.Value).Year;
            string strDenngay = Convert.ToDateTime(txtDenngay.Value).Month + "/" + Convert.ToDateTime(txtDenngay.Value).Day
                + "/" + Convert.ToDateTime(txtDenngay.Value).Year;
            OleDbConnection cnn;
            connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=khambenh.mdb;";
            cnn = new OleDbConnection(connetionString);
            try
            {
                sql = "select a.makhoa,a.tenkhoa,b.sl  from "
                + "  KHOA a left join "
                + " (SELECT KHOA.MAKHOA, KHOA.TENKHOA,count(DONTHUOC.KHOA) as SL "
                + " FROM KHOA LEFT JOIN DONTHUOC ON KHOA.MAKHOA = DONTHUOC.KHOA where  "
                + " (DONTHUOC.ngaykham >= #" + strTungay + "# and DONTHUOC.ngaykham <= #" + strDenngay + "#)"
                + " group by khoa.makhoa,khoa.tenkhoa) b on a.makhoa = b.makhoa order by a.makhoa;";
                OleDbCommand myAccessCommand = new OleDbCommand(sql, cnn);
                OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
                cnn.Open();
                myDataAdapter.Fill(dsDonthuoc, "bctonghop");
                cnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
                      
            ReportDocument cr = new ReportDocument();
            Cursor.Current = Cursors.Default;
            Report f = new Report();
            cr.Load("crpBaocaotonghop.rpt");
            cr.SetDataSource(dsDonthuoc);
            foreach (CrystalDecisions.CrystalReports.Engine.FormulaFieldDefinition thisFormulaField in cr.DataDefinition.FormulaFields)
            {
                switch (thisFormulaField.Name)
                {

                    case "tungay":

                        thisFormulaField.Text = "'Từ ngày " + Convert.ToDateTime(txtTungay.Value).Day + " tháng " + Convert.ToDateTime(txtTungay.Value).Month + " năm " + Convert.ToDateTime(txtTungay.Value).Year + "    "
                            + " Đến ngày " + Convert.ToDateTime(txtDenngay.Value).Day + " tháng " + Convert.ToDateTime(txtDenngay.Value).Month + " năm " + Convert.ToDateTime(txtDenngay.Value).Year + "'";
                        break;

                }
            }
            f.crpView.ReportSource = cr;
            f.Show();
        }

        private void btnIndanhsach_Click(object sender, EventArgs e)
        {
            DataRow newRow = null;
            Dataset.dsDonthuoc dsDonthuoc = new Dataset.dsDonthuoc();
           
            for (int i = 1; i < fa.Rows.Count; i++)
            {

                newRow = dsDonthuoc.Tables["donthuoc"].NewRow();
                newRow["hoten"] = fa.Rows[i]["HOTEN"];
                newRow["namsinh"] = fa.Rows[i]["NAMSINH"];
                if (fa.Rows[i]["GIOITINH"].ToString() == "0") { newRow["gioitinh"] = "Nữ"; } else { newRow["gioitinh"] = "Nam"; }
                newRow["diachi"] = fa.Rows[i]["DIACHI"];
                newRow["chuandoan"] = fa.Rows[i]["CHUANDOAN"];
                newRow["loidan"] = fa.Rows[i]["LOIDAN"];
                newRow["ngaykham"] = fa.Rows[i]["NGAYKHAM"];
                dsDonthuoc.Tables["donthuoc"].Rows.Add(newRow);
      
            }
            ReportDocument cr = new ReportDocument();
            Cursor.Current = Cursors.Default;
            Report f = new Report();
            cr.Load("crpdsbenhnhan.rpt");
            cr.SetDataSource(dsDonthuoc);
            foreach (CrystalDecisions.CrystalReports.Engine.FormulaFieldDefinition thisFormulaField in cr.DataDefinition.FormulaFields)
            {
                switch (thisFormulaField.Name)
                {
                 
                    case "tungay":

                        thisFormulaField.Text = "'Từ ngày " + Convert.ToDateTime(txtTungay.Value).Day + " tháng " + Convert.ToDateTime(txtTungay.Value).Month + " năm " + Convert.ToDateTime(txtTungay.Value).Year + "    "
                            +  " Đến ngày " + Convert.ToDateTime(txtDenngay.Value).Day + " tháng " + Convert.ToDateTime(txtDenngay.Value).Month + " năm " + Convert.ToDateTime(txtDenngay.Value).Year + "'" ;
                        break;

                }
            }                  
            f.crpView.ReportSource = cr;
            f.Show();
            
        }

       
       
    }
}
